记一次Oracle 跨库查询缓慢的经历

您所在的位置：网站首页 › oracle dblink查询优化 › 记一次Oracle 跨库查询缓慢的经历

记一次Oracle 跨库查询缓慢的经历

2023-08-13 20:42| 来源: 网络整理| 查看: 265

Oracle dblink的知识点，请前往oracle 中 dblink 的简单使用学习。

1、查询语句：

SELECT DISTINCT(G.GOODS_ID),BAR_CODE,GOODS_NAME,BRAND_ID FROM DBUSRDAS1.GOODS@UAT_DAHQ G LEFT JOIN DBUSRDAS1.GOODS_SHOP@UAT_DAHQ GS ON G.GOODS_ID = GS.GOODS_ID WHERE NOT EXISTS (SELECT GOODS_ID FROM MD_PARTNER_GOODS_LINKED WHERE VENDOR_CODE ='8518' AND G.GOODS_ID = GOODS_ID) AND VENDER_ID = '8518' AND GOODS_STATUS NOT IN (1,2,3)

查询效率如下图：

2、当在上面的语句基础上进一步缩小查询范围时,即，sql检索异常缓慢：

SELECT DISTINCT(G.GOODSID),BARCODE,GOODSNAME,BRANDID FROM DBUSRDAS1.GOODS@UAT_DAHQ G LEFT JOIN DBUSRDAS1.GOODSSHOP@UAT_DAHQ GS ON G.GOODSID = GS.GOODSID WHERE NOT EXISTS (SELECT GOODS_ID FROM MD_PARTNER_GOODS_LINKED WHERE VENDOR_CODE ='8518' AND G.GOODSID = GOODS_ID) AND VENDERID = '8518' AND GOODSSTATUS NOT IN (1,2,3) AND BRANDID = '15563'

2.1、执行语句耗时如下：

2.2、sql执行计划如下：

3、优化后的sql语句

SELECT /*+ leading(G) use_nl(gs) */ distinct(G.GOODSID),BARCODE,GOODSNAME,BRANDID FROM DBUSRDAS1.GOODS@UAT_DAHQ G LEFT JOIN DBUSRDAS1.GOODSSHOP@UAT_DAHQ GS ON G.GOODSID = GS.GOODSID WHERE NOT EXISTS (SELECT * FROM MD_PARTNER_GOODS_LINKED WHERE DELETED=0 AND VENDOR_CODE ='8518' AND G.GOODSID = GOODS_ID) AND VENDERID = '8518' AND GOODSSTATUS NOT IN (1,2,3) AND BRANDID='15563'

3.1、执行效率如下：

3.2、sql执行计划如下：

/*+ leading(G) use_nl(gs) */ 的作用是把link表和本地表通过hash的方式关联出来。相关leading user_nl参考链接： 1、https://blog.csdn.net/huoshuyinhua/article/details/80450913 2、https://blog.csdn.net/wushanyun1989/article/details/10330541

【本文地址】

记一次Oracle 跨库查询缓慢的经历

记一次Oracle 跨库查询缓慢的经历

今日新闻

推荐新闻